package com.agency8x.model;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;

import com.agency8x.bean.ResultInfoBean;
import com.agency8x.model.base.BaseMiddlemanModel;
import com.agency8x.util.DateTimeUtil;
import com.agency8x.util.RegexUtil;
import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.ICallback;
import com.jfinal.plugin.activerecord.Page;

/**
 * Agency model. 数据库字段名建议使用驼峰命名规则，便于与 java 代码保持一致，如字段名： userId
 */
@SuppressWarnings("serial")
public class MiddlemanModel extends BaseMiddlemanModel<MiddlemanModel> {

    public static final MiddlemanModel me = new MiddlemanModel().dao();

    /**
     * 所有 sql 与业务逻辑写在 Service 中，在此放在 Model 中仅为示例
     */
    public Page<MiddlemanModel> paginate(int pageNumber, int pageSize, String where) {
        String sql = "SELECT u.user_id, u.nickname,u.mobile,u.email,agt.top_agent_id,agt.is_free_regist_agent,b.child_agent_count,b.child_user_count,d.blance,u.user_status, u.regist_time, agt.update_time, "
                + "c.withdraw_amount_today,c.recharge_amount_today,c.withdraw_amount_total,c.recharge_amount_total ";
        String sqlFrom = "  FROM dbo.t_user_agent agt LEFT JOIN t_user u ON agt.user_id=u.user_id "
                + " left join t_user_agent_statistics b on agt.user_id =b.user_id "
                + " left join t_user_finance_statistics c on agt.user_id = c.user_id "
                + " left join t_user_finance_account d on agt.user_id = d.user_id "
                + " WHERE agt.top_agent_id > 0";
        if (where != null && !"".equals(where) && !"null".equals(where)) {
            if (RegexUtil.isNumeric(where)) {
                sqlFrom = sqlFrom + " and u.user_id=" + where;
            }
        }
        return paginate(pageNumber, pageSize, sql, sqlFrom);
    }

    /**
     * 获取所有下线代理
     *
     * @param pageNumber
     * @param pageSize
     * @param top_agent_id
     * @param word
     * @return
     * @since 1.0 2017-5-27
     */
    public Page<MiddlemanModel> getAllChildAgentPaginate(int pageNumber, int pageSize, Integer top_agent_id, String word, String date1, String date2) {
        String sql = "SELECT u.user_id, u.nickname, acc.blance, acc.frozen_blance, fsta.income_amount_today,fsta.income_amount_total, fsta.withdraw_amount_today, fsta.withdraw_amount_total, fsta.recharge_amount_today, fsta.recharge_amount_total, asta.child_agent_count, u.regist_time, agt.is_agent_date";
        String sqlFrom = "FROM t_user u LEFT JOIN dbo.t_user_finance_account acc ON u.user_id=acc.user_id LEFT JOIN dbo.t_user_finance_statistics fsta ON u.user_id=fsta.user_id LEFT JOIN dbo.t_user_agent_statistics asta ON u.user_id=asta.user_id LEFT JOIN dbo.t_user_agent agt ON u.user_id=agt.user_id";
        String sqlWhere = " WHERE u.is_agent=1";
        String sqlOrder = " ORDER BY u.user_id desc";
        if (StrKit.notBlank(date1) && DateTimeUtil.validateDate(date1)) {
            sqlWhere += " and u.regist_date >='" + date1 + "'";
        }
        if (StrKit.notBlank(date2) && DateTimeUtil.validateDate(date2)) {
            sqlWhere += " and u.regist_date <='" + date2 + "'";
        }

        if (StrKit.isBlank(word)) {
            sqlWhere += " and u.top_agent_id=" + top_agent_id;
        } else {
            sqlWhere += " and (u.user_id like '%" + word + "%' or u.nickname like '%" + word + "%')";
        }
        sqlFrom += sqlWhere + sqlOrder;
        return paginate(pageNumber, pageSize, sql, sqlFrom);
    }

    public MiddlemanModel MiddlemanEdit(String user_id) {
        String sql = "SELECT u.user_id, u.nickname,u.mobile,u.email,u.sex, agt.top_agent_id, agt.is_free_regist_agent, u.user_status, u.regist_time, agt.update_time ";
        String sqlFrom = "  FROM dbo.t_user_agent agt LEFT JOIN t_user u ON agt.user_id=u.user_id WHERE agt.top_agent_id > 0  and u.user_id = ? ";
        return me.findFirst(sql + sqlFrom, user_id);
    }

    /**
     * 获取下级代理
     */
    public Page<MiddlemanModel> parent1inate(int pageNumber, int pageSize, Integer top_agent_id, String where) {
        String sql = "select a.user_id,a.nickname,a.user_status,b.child1_agent_count ,c.blance,d.income_amount_today,d.income_amount_month,"
                + "d.income_amount_year,d.withdraw_amount_total,d.recharge_amount_total,d.income_amount_total,d.withdraw_amount_today,d.recharge_amount_today ,a.top_agent_id ";
        String sqlFrom = "from t_user a left join t_user_agent_statistics b on a.user_id =b.user_id "
                + "left join t_user_finance_account c on a.user_id =c.user_id "
                + "left join t_user_finance_statistics d on a.user_id = d.user_id "
                + "where a.is_agent = 1 ";
        if (top_agent_id > 0) {
            sqlFrom = sqlFrom + "and a.top_agent_id=" + top_agent_id;
        }
        if (StrKit.notBlank(where) && !"null".equals(where)) {
            if (RegexUtil.isNumeric(where)) {
                sqlFrom = sqlFrom + "and a.user_id=" + where;
            }
        }

        return paginate(pageNumber, pageSize, sql, sqlFrom);
    }

    public ResultInfoBean createMiddleman(Integer topAgentId) {
        ResultInfoBean resultbean = new ResultInfoBean();
        Db.execute(new ICallback() {
            @Override
            public Object call(Connection conn) {
                try {
                    // 返回 1表示成功;0表示失败
                    CallableStatement proc = conn.prepareCall("{?=call proc_user_regist_topagent(?,?)}"); // see: http://blog.sina.com.cn/s/blog_4d3559c70100mi3a.html
                    proc.registerOutParameter(1, java.sql.Types.INTEGER);
                    proc.setInt(2, topAgentId);
                    proc.registerOutParameter(3, java.sql.Types.VARCHAR);
                    proc.execute();
                    Integer userId = proc.getInt(1);
                    String strout = proc.getString(3);
                    resultbean.setResult(userId > 0);
                    resultbean.setMessage(strout);
                    resultbean.setObjectParam(userId);
                } catch (SQLException e) {
                    resultbean.setResult(false);
                    resultbean.setMessage(e.getMessage());
                }
                return null;
            }
        });
        return resultbean;
    }

    public MiddlemanModel getRecord(Integer top_agent_id) {
        return me.findFirst("SELECT TOP 1 u.user_id, u.nickname, u.realname, u.sex, u.mobile, u.regist_time, agt.top_agent_id, acc.blance, acc.frozen_blance, (asta.child1_agent_count_today+asta.child2_agent_count_today) AS child_agent_count_today, asta.child_agent_count FROM t_user u LEFT JOIN dbo.t_user_agent agt ON u.user_id=agt.user_id LEFT JOIN dbo.t_user_finance_account acc ON u.user_id=acc.user_id LEFT JOIN dbo.t_user_agent_statistics asta ON u.user_id=asta.user_id WHERE agt.top_agent_id=?", top_agent_id);
    }

    public boolean updateMiddleman(Integer user_id, String nickname, String email, String mobile, String username, String password, Integer sex, Integer is_free_regist_agent) {
        int count = Db.update("update t_user set nickname=?, email=?, mobile=?, user_name=?, password=?, sex=? where user_id=?", nickname, email, mobile, username, password, sex, user_id);
        count = Db.update("update t_user_agent set is_free_regist_agent=? where user_id=?", is_free_regist_agent, user_id);
        return count > 0;

    }
    public String getRegistTimeText(){
        return DateTimeUtil.formatSqlServerDateTime(getRegistTime());
    }
}
